package org.zjx.swing.db;

import com.sun.jna.platform.win32.WinDef;
import lombok.Data;
import org.zjx.swing.bean.ParagraphBean;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ParagraphBeanDB {
    private static boolean isTableCreated = false; // 确保表只创建一次
    private SQLiteHelper dbHelper;

    public ParagraphBeanDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTables();
            isTableCreated = true;
        }
    }

    // 创建三张表，启用外键约束
    private void createTables() {
        String[] sqls = {
                "PRAGMA foreign_keys = ON;",
                "CREATE TABLE IF NOT EXISTS paragraph (id INTEGER PRIMARY KEY AUTOINCREMENT);",
                "CREATE TABLE IF NOT EXISTS category (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "paragraph_id INTEGER NOT NULL," +
                        "category TEXT NOT NULL," +
                        "FOREIGN KEY(paragraph_id) REFERENCES paragraph(id) ON DELETE CASCADE);",
                "CREATE TABLE IF NOT EXISTS word (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "category_id INTEGER NOT NULL," +
                        "name TEXT NOT NULL," +
                        "x INTEGER," +
                        "y INTEGER," +
                        "width INTEGER," +
                        "height INTEGER," +
                        "FOREIGN KEY(category_id) REFERENCES category(id) ON DELETE CASCADE);"
        };
        for (String sql : sqls) {
            dbHelper.executeUpdate(sql, new Object[0]);
        }
    }

    // 插入整个 ParagraphBean 结构（包含事务）
    public void insert(ParagraphBean paragraph) {
        try {
            dbHelper.beginTransaction();
            // 1. 插入 paragraph 主表
            String insertParagraph = "INSERT INTO paragraph DEFAULT VALUES;";
            int paragraphId = dbHelper.executeUpdateAndReturnId(insertParagraph, new Object[0]);

            // 2. 插入 category 和 word 子表
            for (ParagraphBean.Categories category : paragraph.getChildren()) {
                String insertCategory = "INSERT INTO category(paragraph_id, category) VALUES(?, ?)";
                Object[] categoryParams = { paragraphId, category.getCategory() };
                int categoryId = dbHelper.executeUpdateAndReturnId(insertCategory, categoryParams);

                for (ParagraphBean.Word word : category.getWords()) {
                    String insertWord = "INSERT INTO word(category_id, name, x, y, width, height) VALUES(?, ?, ?, ?, ?, ?)";
                    WinDef.RECT rect = word.getRect();
                    Object[] wordParams = { categoryId, word.getName(), rect.left, rect.top, rect.right - rect.left, rect.bottom - rect.top };
                    dbHelper.executeUpdate(insertWord, wordParams);
                }
            }
            dbHelper.commitTransaction();
        } catch (SQLException e) {
            dbHelper.rollbackTransaction();
            throw new RuntimeException("Insert failed: " + e.getMessage());
        }
    }

    // 根据 paragraphId 删除整个段落
    public void delete(int paragraphId) {
        String sql = "DELETE FROM paragraph WHERE id = ?";
        dbHelper.executeUpdate(sql, new Object[]{ paragraphId });
    }

    // 查询所有段落（带嵌套结构）
    public List<ParagraphBean> findAll() {
        List<ParagraphBean> paragraphs = new ArrayList<>();
        // 1. 查询所有 paragraph
        String sqlParagraph = "SELECT id FROM paragraph";
        List<Integer> paragraphIds = dbHelper.executeQueryForIds(sqlParagraph);

        // 2. 为每个 paragraph 填充子数据
        for (Integer id : paragraphIds) {
            ParagraphBean bean = findById(id);
            if (bean != null) paragraphs.add(bean);
        }
        return paragraphs;
    }

    // 根据 ID 查询完整段落结构
    public ParagraphBean findById(int paragraphId) {
        ParagraphBean paragraph = new ParagraphBean();
        // 查询 categories
        String sqlCategory = "SELECT id, category FROM category WHERE paragraph_id = ?";
        List<CategoryRow> categories = dbHelper.executeQuery(sqlCategory, new Object[]{ paragraphId }, CategoryRow.class);

        for (CategoryRow catRow : categories) {
            ParagraphBean.Categories category = new ParagraphBean.Categories();
            category.setCategory(catRow.getCategory());

            // 查询 words
            String sqlWord = "SELECT name, x, y, width, height FROM word WHERE category_id = ?";
            List<WordRow> words = dbHelper.executeQuery(sqlWord, new Object[]{ catRow.getId() }, WordRow.class);
            for (WordRow wordRow : words) {
                WinDef.RECT rect = new WinDef.RECT();
                rect.left = wordRow.getX();
                rect.top = wordRow.getY();
                rect.right = wordRow.getX() + wordRow.getWidth();
                rect.bottom = wordRow.getY() + wordRow.getHeight();
                category.addWord(new ParagraphBean.Word(wordRow.getName(), rect));
            }
            paragraph.add(category);
        }
        return paragraph;
    }

    // 清空所有数据
    public void clearAll() {
        dbHelper.executeUpdate("DELETE FROM word", new Object[0]);
        dbHelper.executeUpdate("DELETE FROM category", new Object[0]);
        dbHelper.executeUpdate("DELETE FROM paragraph", new Object[0]);
    }

    // 辅助类：用于结果集映射
    @Data
    private static class CategoryRow {
        private int id;
        private String category;
    }

    @Data
    private static class WordRow {
        private String name;
        private int x;
        private int y;
        private int width;
        private int height;
    }
}
